﻿Imports System.Data.OleDb
Imports DTO

Public Class KhachHang_DAO

#Region "Thêm khách hàng"
    Public Shared Function ThemKhachHang(ByVal khachang As KhachHang_DTO) As Integer
        Dim sql As String = String.Format("insert into KhachHang (TenKH,DiaChi,DienThoai,Email,CMND,TienNo,MaTinhTrang) values('{0}','{1}','{2}','{3}','{4}',{5},{6})", khachang.TenKH, khachang.DiaChi, khachang.DienThoai, khachang.Email, khachang.CMND, khachang.TienNo, khachang.MaTinhTrang)
        Dim flag As Integer = DataProvider.ExcuteNonQuery(sql)
        Return flag
    End Function
#End Region

#Region "Lấy khách hàng theo CMND"
    Public Shared Function LayKhachHang(ByVal cmnd As Long) As DataTable
        Dim sql As String = String.Format("select * from KhachHang where CMND = '{0}'", cmnd)
        Dim tb As DataTable = DataProvider.ExcuteReader(sql)
        Return tb
    End Function
#End Region

#Region "Tìm kiếm khách hàng"
    Public Shared Function TraCuuKhachHang(ByVal KH_CRT As KhachHang_CRT) As DataTable

        Dim strDKTenKH = "1=1"
        Dim strDKDiaChi = "1=1"
        Dim strDKDienThoai = "1=1"
        Dim strDKEmail = "1=1"

        If (KH_CRT.TenKH <> "") Then
            strDKTenKH = String.Format("kh.TenKH like '%{0}%' ", KH_CRT.TenKH)

        End If

        If (KH_CRT.DiaChi <> "") Then
            strDKDiaChi = String.Format("kh.DiaChi like '%{0}%' ", KH_CRT.DiaChi)
        End If

        If (KH_CRT.DienThoai <> "") Then
            strDKDienThoai = String.Format("kh.DienThoai like '%{0}%' ", KH_CRT.DienThoai)
        End If

        If (KH_CRT.Email <> "") Then
            strDKEmail = String.Format("kh.Email like '%{0}%' ", KH_CRT.Email)
        End If

        Dim strDKWhere As String = " Where kh.MaTinhTrang = tt.Ma "
        strDKWhere += " and " + strDKTenKH
        strDKWhere += " and " + strDKDiaChi
        strDKWhere += " and " + strDKDienThoai
        strDKWhere += " and " + strDKEmail


        Dim strSQL As String = "Select kh.TenKH,kh.DiaChi,kh.DienThoai,kh.Email,kh.CMND,kh.TienNo,tt.Ten From KhachHang kh,TinhTrangKhachHang tt "
        strSQL += strDKWhere
        strSQL += " Order by TenKH "

        Dim tb As DataTable = DataProvider.ExcuteReader(strSQL)
        Return tb


    End Function
#End Region

#Region "Load Danh sách khách hàng"
    Public Shared Function DanhSachKhachHang() As DataTable
        Dim sql As String = String.Format("select kh.TenKH,kh.DiaChi,kh.DienThoai,kh.Email,kh.CMND,kh.TienNo,tt.Ten from KhachHang kh,TinhTrangKhachHang tt where kh.MaTinhTrang = tt.Ma")
        Dim tb As DataTable = DataProvider.ExcuteReader(sql)
        Return tb
    End Function
#End Region

#Region "Báo cáo công nợ khách hàng"
    Public Shared Function BaoCao(ByVal thangbaocao As Integer) As DataTable
        Dim baocaotb As New DataTable()
        baocaotb.Columns.Add("TenKhachHang")
        baocaotb.Columns.Add("NoDau")
        baocaotb.Columns.Add("TienMuaTrongThang")
        baocaotb.Columns.Add("TienTraTrongThang")
        baocaotb.Columns.Add("NoCuoi")

        Dim sql As String = "select MaKH,TenKH,TienNo from KhachHang"
        Dim khachhangtb As New DataTable()
        khachhangtb = DataProvider.ExcuteReader(sql)

        For Each row As DataRow In khachhangtb.Rows
            Dim makh As Integer = Integer.Parse(row.Item(0).ToString())
            Dim tenkh As String = row.Item(1).ToString()
            Dim nocuoi As Double = Double.Parse(row.Item(2).ToString())
            Dim thanghientai As Integer = Integer.Parse(Date.Today.Month.ToString())
            While thanghientai >= thangbaocao
                sql = String.Format("select sum(hd.TongTien) from HoaDon hd,KhachHang kh where kh.MaKH = hd.MaKH and month(hd.NgayLap) = {0} and kh.MaKH = {1}", thanghientai, makh)
                Dim tienmuatrongthang As Double
                Dim ojb As Object = DataProvider.ExcuteScalar(sql).ToString()
                If ojb <> Nothing Then
                    tienmuatrongthang = Double.Parse(ojb)
                Else
                    tienmuatrongthang = 0
                End If

                sql = String.Format("select sum(pt.TienThu) from KhachHang kh,PhieuThu pt where kh.MaKH = pt.MaKh and month(pt.NgayThu) = {0} and kh.MaKH = {1}", thanghientai, makh)
                Dim tientratrongthang As Double
                ojb = DataProvider.ExcuteScalar(sql).ToString()
                If ojb <> Nothing Then
                    tientratrongthang = Double.Parse(ojb)
                Else
                    tientratrongthang = 0
                End If


                Dim nodau As Double = nocuoi + tientratrongthang - tienmuatrongthang

                If thanghientai = thangbaocao Then
                    Dim row1 As DataRow = baocaotb.NewRow()
                    row1("TenKhachHang") = tenkh.ToString()
                    row1("NoDau") = nodau.ToString()
                    row1("TienMuaTrongThang") = tienmuatrongthang.ToString()
                    row1("TienTraTrongThang") = tientratrongthang.ToString()
                    row1("NoCuoi") = nocuoi.ToString()
                    baocaotb.Rows.Add(row1)
                End If
                thanghientai = thanghientai - 1
                nocuoi = nodau
            End While
        Next
        Return baocaotb
    End Function
#End Region

End Class
